﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;

using WAF.DAO;

namespace WAF.EntityFramework
{
    /// <summary>
    /// 完成实体类增，删，改，查等常用功能
    /// 利用反射读取实体类字段信息，并生成SQL语句发送到数据库执行
    /// 实体类对应的数据库表有且只能有一个主键(long id)
    /// </summary>
    public class EntitySession
    {
        /***************************************** Public *****************************************/
        
        #region Constructor

        /// <summary>
        /// 通用数据库访问组件
        /// </summary>
        private DbAccess dbAccess;

        /// <summary>
        /// 使用默认数据库类型
        /// </summary>
        public EntitySession() : this(DbAccess.DefaultDbType, DbAccess.DefaultConnectionString) {}

        /// <summary>
        /// 使用指定数据库类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="conString"></param>
        public EntitySession(DbAccess.DatabaseType dbType, string conString)
        {
            dbAccess = new DbAccess(dbType, conString);
        }

        #endregion

        #region Insert

        /// <summary>
        /// 向数据库中增加一条实体类记录
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>实体类主键(id)的值</returns> 
        public long Insert(Entity entity)
        {
            //插入记录之前的操作
            EntitySessionEvent.BeforeInsert(entity);

            //增加实体类记录
            long autoIncrementID = this.InsertEntity(entity);

            //插入记录之后的操作
            EntitySessionEvent.AfterInsert(entity);

            //返回实体类主键(id)的值
            return autoIncrementID;
        }

        #endregion

        #region Update

        /// <summary>
        /// 更新数据库中的一条实体类记录
        /// </summary>
        /// <param name="entity">实体类</param>
        public void Update(Entity entity)
        {
            //更新记录之前的操作
            EntitySessionEvent.BeforeUpdate(entity);

            //更新实体类记录
            this.UpdateEntity(entity);

            //更新记录之后的操作
            EntitySessionEvent.AfterUpdate(entity);
        }

        #endregion

        #region Delete

        /// <summary>
        /// 从数据库中删除一条实体类记录
        /// </summary>
        /// <param name="entity">实体类</param>
        public void Delete(Entity entity)
        {
            //删除记录之前的操作
            EntitySessionEvent.BeforeDelete(entity);

            //删除实体类记录
            this.DeleteEntity(entity);

            //删除记录之后的操作
            EntitySessionEvent.AfterDelete(entity);
        }

        #endregion

        #region Save

        /// <summary>
        /// 保存实体数据（如果该实体已存在则执行增加操作，否则执行更新操作）
        /// </summary>
        /// <param name="entity">实体类</param>
        public void Save(Entity entity)
        {
            if (entity.Id == 0)
                this.Insert(entity);
            else
                this.Update(entity);
        }

        #endregion

        #region Select

        /// <summary>
        /// 创建实体类并获取实体类各字段值
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="id">主键值</param>
        /// <returns>实体类</returns>
        public T Select<T>(long id) where T : Entity
        {
            return (T)this.Select(typeof(T), id);
        }

        /// <summary>
        /// 创建实体类并获取实体类各字段值
        /// </summary>
        /// <param name="entityName">实体类名称</param>
        /// <param name="id">主键值</param>
        /// <returns>实体类</returns>
        public object Select(string entityName, long id)
        {
            return this.Select(Type.GetType(entityName), id);
        }

        /// <summary>
        /// 创建实体类并获取实体类各字段值
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="id">主键值</param>
        /// <returns>实体类</returns>
        public object Select(Type entityType, long id)
        {
            string createSql = (new StringBuilder()).Append("SELECT * FROM ").Append(entityType.Name).Append(" WHERE id=").Append(id).ToString();

            ArrayList entities = this.SelectEntity(entityType, createSql, null);

            return entities.Count == 0 ? null : entities[0];
        }

        /// <summary>
        /// 创建满足检索条件的实体类数组
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="selectSql">检索SQL语句（如果为空则返回所有字段的所有数据）</param>
        /// <param name="parms">SQL语句参数</param>
        /// <returns>实体类数组</returns>
        public T[] Select<T>(string selectSql = "", DbParameters parms = null) where T : Entity
        {
            return (T[])this.SelectEntity(typeof(T), selectSql, parms).ToArray(typeof(T));
        }

        /// <summary>
        /// 创建满足检索条件的实体类数组
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="selectSql">检索SQL语句（如果为空则返回所有字段的所有数据）</param>
        /// <param name="parms">SQL语句参数</param>
        /// <returns>实体类数组</returns>
        public ArrayList Select(Type entityType, string selectSql = "", DbParameters parms = null)
        {
            return this.SelectEntity(entityType, selectSql, parms);
        }

        #endregion

        #region Exist

        /// <summary>
        /// 查询数据库中相应实体类记录是否存在
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>存在返回true,不存在返回false</returns>
        public bool Exist(Entity entity)
        {
            string existSql = (new StringBuilder()).Append("SELECT count(*) FROM ").Append(entity.GetType().Name).Append(" WHERE id=").Append(entity.Id).ToString();

            return int.Parse(dbAccess.ExecuteScalar(existSql).ToString()) == 1 ? true : false;
        }

        /// <summary>
        /// 查询数据库中指定实体类字段是否存在
        /// </summary>
        /// <param name="entityType">实体类类型</param>
        /// <param name="uniqueFieldName">要检查的字段名</param>
        /// <param name="uniqueFieldValue">要检查的字段值</param>
        /// <returns>存在返回true,不存在返回false</returns>
        public bool Exist(Type entityType, string uniqueFieldName, object uniqueFieldValue)
        {
            EntityFieldAttribute fAttribute = EntitySession.GetFieldCustomAttributes(entityType.GetProperty(uniqueFieldName));
            DbParameters parms = new DbParameters("@parm", uniqueFieldValue, fAttribute.FieldDbType);

            string sql = (new StringBuilder()).Append("SELECT count(*) FROM ").Append(entityType.Name)
                                              .Append(" WHERE ").Append(uniqueFieldName).Append("=@parm").ToString();

            object obj = dbAccess.ExecuteScalar(sql, parms);

            return int.Parse(obj.ToString()) >= 1 ? true : false;
        }

        #endregion

        #region Counts

        /// <summary>
        /// 指定数据库表中的记录总数
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <returns>记录总数</returns>
        public int Counts(Type entityType)
        {
            string sql = (new StringBuilder()).Append("SELECT count(*) FROM ").Append(entityType.Name).ToString();

            return int.Parse(dbAccess.ExecuteScalar(sql).ToString());
        }

        #endregion

        #region DbAccess

        /// <summary>
        /// 返回数据库访问组件，以便执行SQL语句
        /// </summary>
        /// <returns>数据库访问组件</returns>
        public DbAccess DbAccess
        {
            get
            {
                return this.dbAccess;
            }
        }

        #endregion

        #region OpenConnection

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            dbAccess.Open();
        }

        #endregion

        #region CloseConnection

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            dbAccess.Close();
        }

        #endregion

        #region BeginTransaction

        /// <summary>
        /// 使用默认数据库类型打开连接并开始事务
        /// </summary>
        public void BeginTransaction()
        {
            dbAccess.Open();
            dbAccess.BeginTransaction();
        }

        #endregion

        #region CommitTtransaction

        /// <summary>
        /// 提交事务并关闭连接
        /// </summary>
        public void CommitTtransaction()
        {
            dbAccess.CommitTtransaction();
            dbAccess.Close();
        }

        #endregion

        #region RollbackTtransaction

        /// <summary>
        /// 回滚事务并关闭连接
        /// </summary>
        public void RollbackTtransaction()
        {
            dbAccess.RollbackTtransaction();
            dbAccess.Close();
        }

        #endregion

        /************************************** Public Static **************************************/

        #region ValidateField

        public string ValidateField(PropertyInfo pInfo, object fieldValue)
        {
            EntityFieldAttribute fAttribute = EntitySession.GetFieldCustomAttributes(pInfo);

            string errorMsg = null;
            string fieldLabel = "[" + fAttribute.Label + "]";

            

            //检查字段是否能为空
            if (!fAttribute.IsNullable && (System.DBNull.Value.Equals(fieldValue) || fieldValue.ToString().Trim() == ""))
                errorMsg = fieldLabel + "不能为空！";
            //检查字段是否唯一
            else if (fAttribute.IsUnique && this.Exist(pInfo.ReflectedType, pInfo.Name, fieldValue))
                errorMsg = fieldLabel + "为 " + fieldValue.ToString() + " 的记录已经存在！";

            return errorMsg;
        }

        #endregion

        #region GetCustomAttributes

        /// <summary>
        /// 返回指定实体类的自定义属性
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>类的自定义属性</returns>
        public static EntityTableAttribute GetTableCustomAttributes(Type entityType)
        {
            return entityType.GetCustomAttributes(typeof(EntityTableAttribute), false)[0] as EntityTableAttribute;
        }

        /// <summary>
        /// 返回指定字段的自定义属性
        /// </summary>
        /// <param name="pInfo">字段</param>
        /// <returns>字段的自定义属性</returns>
        public static EntityFieldAttribute GetFieldCustomAttributes(PropertyInfo pInfo)
        {
            return pInfo.GetCustomAttributes(typeof(EntityFieldAttribute), false)[0] as EntityFieldAttribute;
        }

        /// <summary>
        /// 返回指定字段的自定义约束属性
        /// </summary>
        /// <param name="pInfo">字段</param>
        /// <returns>字段的自定义约束属性</returns>
        public static EntityFieldConstraintAttribute GetFieldConstraintCustomAttributes(PropertyInfo pInfo)
        {
            return pInfo.GetCustomAttributes(typeof(EntityFieldConstraintAttribute), false)[0] as EntityFieldConstraintAttribute;
        }

        #endregion

        #region GetPropertesOrderBy

        /// <summary>
        /// 返回按实体类中定义顺序排序的属性数组，下标从零开始
        /// </summary>
        /// <param name="entityType">实体类类型</param>
        /// <returns>排序后的属性数组</returns>
        public static PropertyInfo[] GetPropertiesOrder(Type entityType)
        {
            PropertyInfo[] pInfos = entityType.GetProperties();
            PropertyInfo[] orderPropertyInfo = new PropertyInfo[pInfos.Length];

            foreach (PropertyInfo fInfo in pInfos)
            {
                EntityFieldAttribute fAttribute = EntitySession.GetFieldCustomAttributes(fInfo);

                orderPropertyInfo[fAttribute.Ordinal] = fInfo;
            }

            return orderPropertyInfo;
        }

        #endregion
        
        /***************************************** Private *****************************************/

        #region Private

        /// <summary>
        /// 向数据库中增加一条实体类记录
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns>实体类主键(id)的值</returns>
        private long InsertEntity(Entity entity)
        {
            Type entityType = entity.GetType();

            string insertSql = null;
            DbParameters parms = new DbParameters();

            StringBuilder sbField = (new StringBuilder()).Append("INSERT INTO ").Append(entityType.Name).Append("(");
            StringBuilder sbValue = new StringBuilder(" VALUES(");

            foreach (PropertyInfo pInfo in EntitySession.GetPropertiesOrder(entityType))
            {
                EntityFieldAttribute fAttribute = EntitySession.GetFieldCustomAttributes(pInfo);

                //跳过主键，因为主键是自增长列
                if (fAttribute.IsPrimaryKey) continue;

                sbField.Append("[").Append(pInfo.Name).Append("],");
                sbValue.Append("@").Append(pInfo.Name).Append(",");

                parms.Add(pInfo.Name, pInfo.GetValue(entity, null), fAttribute.FieldDbType);
            }

            sbField = sbField.Replace(',', ')', sbField.Length - 1, 1);
            sbValue = sbValue.Replace(',', ')', sbValue.Length - 1, 1);

            insertSql = sbField.Append(sbValue.ToString()).Append(";").Append(GetAutoIncrementSql()).ToString();

            return (long)dbAccess.ExecuteScalar(insertSql, parms);
        }

        /// <summary>
        /// 获取指定数据库的最后自增长字段的值
        /// </summary>
        /// <param name="dbType">数据库种类</param>
        /// <returns>最后自增长字段的值</returns>
        private string GetAutoIncrementSql()
        {
            string autoIncrementSql = "";

            switch (dbAccess.CurrentDbType)
            {
                case DbAccess.DatabaseType.MSSQL:
                    autoIncrementSql = "SELECT SCOPE_IDENTITY();";
                    break;
                case DbAccess.DatabaseType.MYSQL:
                    autoIncrementSql = "SELECT LAST_INSERT_ID();";
                    break;
                case DbAccess.DatabaseType.ORACLE:
                    autoIncrementSql = "SELECT sequence.currval FROM DUAL;";
                    break;
                case DbAccess.DatabaseType.SQLITE:
                    autoIncrementSql = "SELECT LAST_INSERT_ROWID();";
                    break;
                case DbAccess.DatabaseType.SYBASE:
                    autoIncrementSql = "SELECT @@IDENTITY;";
                    break;
            }

            return autoIncrementSql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="updateSql"></param>
        /// <param name="parms"></param>
        private void UpdateEntity(Entity entity)
        {
            string pkName = "";
            Type entityType = entity.GetType();
            PropertyInfo[] pInfos = entityType.GetProperties();

            DbParameters parms = new DbParameters();
            StringBuilder sb = (new StringBuilder()).Append("UPDATE ").Append(entityType.Name).Append(" SET ");

            foreach (PropertyInfo pInfo in pInfos)
            {
                EntityFieldAttribute fAttribute = EntitySession.GetFieldCustomAttributes(pInfo);

                object value = pInfo.GetValue(entity, null);

                //跳过主键
                if (fAttribute.IsPrimaryKey)
                {
                    pkName = pInfo.Name;
                    parms.Add(pInfo.Name, value, fAttribute.FieldDbType);
                    continue;
                }
                else if (fAttribute.FieldDbType == DbType.String)
                { 
                    
                }

                sb.Append(pInfo.Name).Append("=").Append("@").Append(pInfo.Name).Append(",");
                parms.Add(pInfo.Name, value, fAttribute.FieldDbType);
            }

            string updateSql = sb.Replace(',', ' ', sb.Length - 1, 1).Append(" WHERE ").Append(pkName).Append("=@").Append(pkName).ToString();

            dbAccess.ExecuteNonQuery(updateSql, parms);
        }

        /// <summary>
        /// 删除数据库中的一条实体类记录
        /// </summary>
        /// <param name="entity">实体类</param>
        private void DeleteEntity(Entity entity)
        {
            string deleteSql = (new StringBuilder()).Append("DELETE FROM ").Append(entity.GetType().Name)
                                                    .Append(" WHERE id=").Append(entity.Id).ToString();

            dbAccess.ExecuteNonQuery(deleteSql);
        }

        /// <summary>
        /// 创建满足搜索条件的实体类数组
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="selectSql">搜索SQL语句（如果为空则返回所有字段的所有数据）</param>
        /// <param name="parms">参数</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>实体类数组</returns>
        private ArrayList SelectEntity(Type entityType, string selectSql = "", DbParameters parms = null)
        {
            if (selectSql == "") selectSql = "SELECT * FROM " + entityType.Name;

            DataTable tb = dbAccess.ExecuteDataSet(selectSql, parms).Tables[0];

            int columnCount = tb.Columns.Count;
            PropertyInfo[] pInfos = new PropertyInfo[columnCount];
            EntityFieldAttribute[] fAttributes = new EntityFieldAttribute[columnCount];

            for (int i = 0; i < columnCount; i++)
            {
                pInfos[i] = entityType.GetProperty(tb.Columns[i].ColumnName);
                fAttributes[i] = EntitySession.GetFieldCustomAttributes(pInfos[i]);
            }

            int rowCount = tb.Rows.Count;
            ArrayList entities = new ArrayList();

            for (int i = 0; i < rowCount; i++)
            {
                object entity = Activator.CreateInstance(entityType);
                
                for (int j = 0; j < columnCount; j++)
                {
                    object obj = tb.Rows[i][j];

                    //如果数据库中字段的值为null，就将字符串字段值设置为空字符串，以防止引用错误
                    //其他类型字段设为初始化值（Int:0，Float:0, DateTime:"0001-01-01 00:00:00"）
                    if (!System.DBNull.Value.Equals(obj))
                    {
                        pInfos[j].SetValue(entity, obj, null);
                    }
                    else if (fAttributes[j].FieldDbType == DbType.String)
                    {
                        pInfos[j].SetValue(entity, (object)String.Empty, null);
                    }
                }

                entities.Add(entity);
            }

            return entities;
        }

        #endregion
    }
}
